# 导包
import pymysql
import requests


class DBUtils(object):
    # 设置类属性
    __conn = None
    __cursor = None

    # 1 创建连接
    @classmethod
    def __get_conn(cls):
        cls.__conn = pymysql.connect(host="211.103.136.244", port=7061, user="student",
                                     password="iHRM_student_2021", database="ihrm", charset="utf8")
        return cls.__conn

    # 2 获取游标
    @classmethod
    def __get_cursor(cls):
        cls.__cursor = cls.__get_conn().cursor()
        return cls.__cursor

    # 3 执行sql语句，并返回结果  sql = "select * FROM t_book;"  "1,2,3,4,5"
    @classmethod
    def sql_perform(cls, sql):

        try:
            cursor = cls.__get_cursor()
            cursor.execute(sql)
            # split()函数的作用是，把字符串转换成列表，如果()里不填的话，就默认以空格为分隔符取出各个元素。
            # 然后再通过下标取到第一个元素，进行判断。判断之前在统一转换成小写字母。
            # 判断sql语句它是查询   还是增删改查
            if sql.split()[0].lower() == "select":
                # 返回结果集
                return cursor.fetchall()
            else:
                # 如果是增删改语句，提交事务，返回影响行数
                cls.__conn.commit()
                print("cursor.rowcount=====", cursor.rowcount)
                return cursor.rowcount

        except Exception as e:
            cls.__conn.rollback()

            print("有异常发生：", e)

        finally:
            cls.__cursor_close()
            cls.__conn_close()

    # 4关闭游标
    @classmethod
    def __cursor_close(cls):
        cls.__cursor.close()

    # 5关闭连接
    @classmethod
    def __conn_close(cls):
        cls.__conn.close()


def test_perform():
    sql = "INSERT INTO t_book (id,title,pub_date) VALUES(224235, '死神永生', '2000-01-01');"
    res = DBUtils.sql_perform(sql)
    print(res)

def test_diaoyong():

    # 添加会话
    secs = requests.Session()
    login_url = "http://ihrm-test.itheima.net/api/sys/login"
    body_data = {
        "mobile":"13800000002",
        "password":"123456"
    }

    # 登录
    res = secs.post(url=login_url,json=body_data)
    print()
    print(res.text)
    print("-------------------------------------------------------------------------------------------------------------------------")

    # 删除员工

    url = "http://ihrm-test.itheima.net/api/sys/user/1063705482939739999"
    secs.delete(url=url)

    #断言
    # sql1 = "select * from bs_user"
    # result = DBUtils.sql_porform(sql1)
    # # print(result[2][19])
    # assert result[1][19] == 1